package persistence;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import domain.Class;
import domain.Discipline;
import domain.PTime;

public class DisciplineHandler {
	
	private final String SEPARATOR = ", ";

	Statement stmt;
	
	

	public DisciplineHandler() throws SQLException {
		Connection conn;
		try {
			conn = DBConnection.getInstance().getDb_conn();
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	}

	public boolean create(Discipline discipline) throws SQLException {

		String sqlComand = "INSERT INTO Disciplines VALUES (";
		String sqlComand2 = "INSERT INTO Prerequisites VALUES (";
		//String sqlComand3 = "INSERT INTO Classes VALUES (";

		try {

			sqlComand += discipline.getName()
					+ SEPARATOR
					+ discipline.getCode()
					+ SEPARATOR
					+ discipline.getPeriod()
					+ SEPARATOR
					+ discipline.getTotalDurationForEachPresentable().toString()
					+ ");";			
			
			stmt.execute(sqlComand);

			for (Discipline disc : discipline.getPrerequisites()) {
				String sqlComandPartial = sqlComand2 + discipline.getCode() + ", " + disc.getCode() + ");";
				stmt.execute(sqlComandPartial);
			}

			// duvida sobre as classes

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public boolean delete(String code) {

		String sqlComand = "DELETE FROM Disciplines WHERE ";
		String sqlComand2 = "DELETE FROM Prerequisites WHERE ";

		try {
			
			sqlComand2 += "code = " + code + ";";			
			stmt.execute(sqlComand2);
			
			sqlComand += "code = " + code + ";";			
			stmt.execute(sqlComand);
			
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public boolean update(String code, String name, int period,
			List<Discipline> prerequisites, List<Class> classes,
			PTime totalDurationForEachPresentable) {

		String sqlComand = "UPDATE Disciplines SET ";

		try {
			if (name != "") sqlComand += "name = " + name + SEPARATOR;
			if (period != -1) sqlComand += "period = " + period + SEPARATOR;
			if (totalDurationForEachPresentable != null) sqlComand += "time = " + totalDurationForEachPresentable.toString();
			
			sqlComand += "WHERE code = " + code + ";";
			
			stmt.execute(sqlComand);

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return false;
	}

	public ArrayList<Discipline> retreive(String code) {

		ResultSet stmtResult;
		ArrayList<Discipline> result = new ArrayList<Discipline>();

		String sqlComand = "SELECT * FROM Disciplines WHERE ";

		try {
			sqlComand += "code = " + code + ";";
			stmtResult = stmt.executeQuery(sqlComand);
			
			while(stmtResult.next()){
				result.add(new Discipline(stmtResult.getString(1), stmtResult.getInt(2), stmtResult.getString(3)));
			}
			
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public ArrayList<String> getAllNames(int period){
		
		ResultSet stmtResult;
		ArrayList<String> result = new ArrayList<String>();

		String sqlComand = "SELECT name FROM Disciplines ";

		try {
			if (period != -1) sqlComand += "WHERE period = " + period + ";";
			else sqlComand += ";";
			
			stmtResult = stmt.executeQuery(sqlComand);
			
			while(stmtResult.next()){
				result.add(stmtResult.getString(1));
			}
			
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;

	}
}
